package com.sxfq.loansupermarket.backsystem.modular.marketing.service.impl;

import com.baomidou.mybatisplus.plugins.Page;
import com.sxfq.loansupermarket.backsystem.common.persistence.model.Role;
import com.sxfq.loansupermarket.backsystem.core.log.LogManager;
import com.sxfq.loansupermarket.backsystem.core.log.factory.LogTaskFactory;
import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.core.support.DateTimeKit;
import com.sxfq.loansupermarket.backsystem.core.support.HttpKit;
import com.sxfq.loansupermarket.backsystem.core.util.CommUtils;
import com.sxfq.loansupermarket.backsystem.core.util.StringUtil;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.CustomerInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.service.ICustomerService;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.marketing.dao.ReservationsDao;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.ReservationsInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.SearchReservationsInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.service.IReservationsService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
@Transactional
public class ReservationsServiceImpl implements IReservationsService {

    @Autowired
    private ReservationsDao dao;

    @Autowired
    private SqlMapper sqlMapper;

    @Autowired
    ICustomerService customerService;

    @Override
    public List<ReservationsInfo> selectPageListByQueryGetMap(Page<ReservationsInfo> page, SearchReservationsInfo searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getCustomerName())) {
            params.put("customerName", searchInfo.getCustomerName());
        }
        if (StringUtil.isNotEmpty(searchInfo.getCustomerPhone())) {
            params.put("customerPhone", searchInfo.getCustomerPhone());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStatus())) {
            params.put("status", searchInfo.getStatus());
        }
        if (StringUtil.isNotEmpty(searchInfo.getTcName())) {
            params.put("tcName", searchInfo.getTcName());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStatuss())) {
            params.put("statuss", searchInfo.getStatuss());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArchiving())) {
            params.put("archiving", searchInfo.getArchiving());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEntryClerk())) {
            params.put("entryClerk", searchInfo.getEntryClerk());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFollower())) {
            params.put("follower", searchInfo.getFollower());
        }
        if (StringUtil.isNotEmpty(searchInfo.getReservations())) {
            params.put("reservations", searchInfo.getReservations());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartRevisitTime())) {
            params.put("startRevisitTime", searchInfo.getStartRevisitTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndRevisitTime())) {
            params.put("endRevisitTime", searchInfo.getEndRevisitTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartAppointmentArrivalTime())) {
            params.put("startAppointmentArrivalTime", searchInfo.getStartAppointmentArrivalTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndAppointmentArrivalTime())) {
            params.put("endAppointmentArrivalTime", searchInfo.getEndAppointmentArrivalTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getIsReturnVisit())) {
            params.put("isReturnVisit", searchInfo.getIsReturnVisit());
        }
        if (StringUtil.isNotEmpty(searchInfo.getAccessDisease())) {
            params.put("accessDisease", searchInfo.getAccessDisease());
        }
//        if (StringUtil.isNotEmpty(searchInfo.getDepartment())) {
//            String sql = "select a.id from role a where a.name='" + searchInfo.getDepartment() + "'";
//            UserDto dto = sqlMapper.selectOne(sql, UserDto.class);
//            if (StringUtil.isNotEmpty(dto.getId())) {
//                params.put("roleid", dto.getId());
//            }
//        }
        if (StringUtil.isNotEmpty(searchInfo.getBelonger())) {
            params.put("belonger", searchInfo.getBelonger());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDepartment())) {
            params.put("department", searchInfo.getDepartment());
        }
        if (!"请选择".equals(searchInfo.getOneChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getOneChannel())) {
                params.put("oneChannel", searchInfo.getOneChannel());
            }
        }
        if (!"请选择".equals(searchInfo.getTwoChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getTwoChannel())) {
                params.put("twoChannel", searchInfo.getTwoChannel());
            }
        }
        if (!"请选择".equals(searchInfo.getThrChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getThrChannel())) {
                params.put("thrChannel", searchInfo.getThrChannel());
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getAppointmentPayService())) {
            params.put("appointmentPayService", searchInfo.getAppointmentPayService());
        }
        if (StringUtil.isNotEmpty(searchInfo.getOutpatientName())) {
            params.put("outpatientName", searchInfo.getOutpatientName());
        }
        if (StringUtil.isNotEmpty(searchInfo.getReservationsNum())) {
            params.put("reservationsNum", searchInfo.getReservationsNum());
        }
        if (StringUtil.isNotEmpty(searchInfo.getWechatNumber())) {
            params.put("wechatNumber", searchInfo.getWechatNumber());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartFilingTime())) {
            params.put("startFilingTime", searchInfo.getStartFilingTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndFilingTime())) {
            params.put("endFilingTime", searchInfo.getEndFilingTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartArrivalTime())) {
            params.put("startArrivalTime", searchInfo.getStartArrivalTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndArrivalTime())) {
            params.put("endArrivalTime", searchInfo.getEndArrivalTime());
        }
        return dao.getQueryPage(page, params);
    }

    /**
     * 建档  界面表格数据
     *
     * @param page
     * @param searchInfo
     * @return
     */
    public List<ReservationsInfo> selectPageListByStatusMap(Page<ReservationsInfo> page, SearchReservationsInfo searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getBelonger())) {
            params.put("belonger", searchInfo.getBelonger());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStatuss())) {
            params.put("statuss", searchInfo.getStatuss());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFollower())) {
            params.put("follower", searchInfo.getFollower());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStatus())) {
            params.put("status", searchInfo.getStatus());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArchiving())) {
            params.put("archiving", searchInfo.getArchiving());
        }
        if (StringUtil.isNotEmpty(searchInfo.getAccessDisease())) {
            params.put("accessDisease", searchInfo.getAccessDisease());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDepartment())) {
            params.put("department", searchInfo.getDepartment());
        }
        if (!"请选择".equals(searchInfo.getOneChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getOneChannel())) {
                params.put("oneChannel", searchInfo.getOneChannel());
            }
        }
        if (!"请选择".equals(searchInfo.getTwoChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getTwoChannel())) {
                params.put("twoChannel", searchInfo.getTwoChannel());
            }
        }
        if (!"请选择".equals(searchInfo.getThrChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getThrChannel())) {
                params.put("thrChannel", searchInfo.getThrChannel());
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getAppointmentPayService())) {
            params.put("appointmentPayService", searchInfo.getAppointmentPayService());
        }
        if (StringUtil.isNotEmpty(searchInfo.getOutpatientName())) {
            params.put("outpatientName", searchInfo.getOutpatientName());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartFilingTime())) {
            params.put("startFilingTime", searchInfo.getStartFilingTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndFilingTime())) {
            params.put("endFilingTime", searchInfo.getEndFilingTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartAppointmentTime())) {
            params.put("startAppointmentTime", searchInfo.getStartAppointmentTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndAppointmentTime())) {
            params.put("endAppointmentTime", searchInfo.getEndAppointmentTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartFirstVisitDate())) {
            params.put("startFirstVisitDate", searchInfo.getStartFirstVisitDate());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndFirstVisitDate())) {
            params.put("endFirstVisitDate", searchInfo.getEndFirstVisitDate());
        }
        if ("0".equals(searchInfo.getPageType())) {
            params.put("condition", "");
            params.put("condition1", "order by t.filing_time desc");
        } else if ("1".equals(searchInfo.getPageType())) {
            params.put("condition", "and t.status ='到店'");
            params.put("condition1", "order by t.first_visit_date desc");
        } else {
            params.put("condition", "and t.status!='到店' and t.one_channel!='直接上门'");
            params.put("condition1", "order by t.appointment_time desc");
        }
        params.put("startIndex", searchInfo.getStartIndex());
        params.put("limitIndex", searchInfo.getLimitIndex());
        return dao.getQueryPages(page, params);
    }


    public int getQueryPagesCount(Page<ReservationsInfo> page, SearchReservationsInfo searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getBelonger())) {
            params.put("belonger", searchInfo.getBelonger());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStatuss())) {
            params.put("statuss", searchInfo.getStatuss());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFollower())) {
            params.put("follower", searchInfo.getFollower());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStatus())) {
            params.put("status", searchInfo.getStatus());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArchiving())) {
            params.put("archiving", searchInfo.getArchiving());
        }
        if (StringUtil.isNotEmpty(searchInfo.getAccessDisease())) {
            params.put("accessDisease", searchInfo.getAccessDisease());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDepartment())) {
            params.put("department", searchInfo.getDepartment());
        }
        if (!"请选择".equals(searchInfo.getOneChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getOneChannel())) {
                params.put("oneChannel", searchInfo.getOneChannel());
            }
        }
        if (!"请选择".equals(searchInfo.getTwoChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getTwoChannel())) {
                params.put("twoChannel", searchInfo.getTwoChannel());
            }
        }
        if (!"请选择".equals(searchInfo.getThrChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getThrChannel())) {
                params.put("thrChannel", searchInfo.getThrChannel());
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getAppointmentPayService())) {
            params.put("appointmentPayService", searchInfo.getAppointmentPayService());
        }
        if (StringUtil.isNotEmpty(searchInfo.getOutpatientName())) {
            params.put("outpatientName", searchInfo.getOutpatientName());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartFilingTime())) {
            params.put("startFilingTime", searchInfo.getStartFilingTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndFilingTime())) {
            params.put("endFilingTime", searchInfo.getEndFilingTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartAppointmentTime())) {
            params.put("startAppointmentTime", searchInfo.getStartAppointmentTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndAppointmentTime())) {
            params.put("endAppointmentTime", searchInfo.getEndAppointmentTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartFirstVisitDate())) {
            params.put("startFirstVisitDate", searchInfo.getStartFirstVisitDate());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndFirstVisitDate())) {
            params.put("endFirstVisitDate", searchInfo.getEndFirstVisitDate());
        }
        if ("0".equals(searchInfo.getPageType())) {
            params.put("condition", "");
            params.put("condition1", "order by t.filing_time desc");
        } else if ("1".equals(searchInfo.getPageType())) {
            params.put("condition", "and t.status ='到店'");
            params.put("condition1", "order by t.first_visit_date desc");
        } else {
            params.put("condition", "and t.status!='到店' and t.one_channel!='直接上门'");
            params.put("condition1", "order by t.appointment_time desc");
        }
        return dao.getQueryPagesCount(page, params);
    }


    /**
     *已到  界面表格数据
     *
     * @param page
     * @param searchInfo
     * @return
     */
    public List<ReservationsInfo> selectPageListByStatusMapYD(Page<ReservationsInfo> page, SearchReservationsInfo searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getBelonger())) {
            params.put("belonger", searchInfo.getBelonger());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStatuss())) {
            params.put("statuss", searchInfo.getStatuss());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFollower())) {
            params.put("follower", searchInfo.getFollower());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStatus())) {
            params.put("status", searchInfo.getStatus());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArchiving())) {
            params.put("archiving", searchInfo.getArchiving());
        }
        if (StringUtil.isNotEmpty(searchInfo.getAccessDisease())) {
            params.put("accessDisease", searchInfo.getAccessDisease());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDepartment())) {
            params.put("department", searchInfo.getDepartment());
        }
        if (!"请选择".equals(searchInfo.getOneChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getOneChannel())) {
                params.put("oneChannel", searchInfo.getOneChannel());
            }
        }
        if (!"请选择".equals(searchInfo.getTwoChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getTwoChannel())) {
                params.put("twoChannel", searchInfo.getTwoChannel());
            }
        }
        if (!"请选择".equals(searchInfo.getThrChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getThrChannel())) {
                params.put("thrChannel", searchInfo.getThrChannel());
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getAppointmentPayService())) {
            params.put("appointmentPayService", searchInfo.getAppointmentPayService());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArrivalStore())) {
            params.put("arrivalStore", searchInfo.getArrivalStore());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartFilingTime())) {
            params.put("startFilingTime", searchInfo.getStartFilingTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndFilingTime())) {
            params.put("endFilingTime", searchInfo.getEndFilingTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartAppointmentTime())) {
            params.put("startAppointmentTime", searchInfo.getStartAppointmentTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndAppointmentTime())) {
            params.put("endAppointmentTime", searchInfo.getEndAppointmentTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartFirstVisitDate())) {
            params.put("startFirstVisitDate", searchInfo.getStartFirstVisitDate());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndFirstVisitDate())) {
            params.put("endFirstVisitDate", searchInfo.getEndFirstVisitDate());
        }
        if ("0".equals(searchInfo.getPageType())) {
            params.put("condition", "");
            params.put("condition1", "order by t.filing_time desc");
        } else if ("1".equals(searchInfo.getPageType())) {
            params.put("condition", "and t.status ='到店'");
            params.put("condition1", "order by t.first_visit_date desc");
        } else {
            params.put("condition", "and t.status!='到店' and t.one_channel!='直接上门'");
            params.put("condition1", "order by t.appointment_time desc");
        }
        params.put("startIndex", searchInfo.getStartIndex());
        params.put("limitIndex", searchInfo.getLimitIndex());
        return dao.getQueryPagesYD(page, params);
    }


    public int getQueryPagesCountYD(Page<ReservationsInfo> page, SearchReservationsInfo searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("page", page);
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getBelonger())) {
            params.put("belonger", searchInfo.getBelonger());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStatuss())) {
            params.put("statuss", searchInfo.getStatuss());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFollower())) {
            params.put("follower", searchInfo.getFollower());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStatus())) {
            params.put("status", searchInfo.getStatus());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArchiving())) {
            params.put("archiving", searchInfo.getArchiving());
        }
        if (StringUtil.isNotEmpty(searchInfo.getAccessDisease())) {
            params.put("accessDisease", searchInfo.getAccessDisease());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDepartment())) {
            params.put("department", searchInfo.getDepartment());
        }
        if (!"请选择".equals(searchInfo.getOneChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getOneChannel())) {
                params.put("oneChannel", searchInfo.getOneChannel());
            }
        }
        if (!"请选择".equals(searchInfo.getTwoChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getTwoChannel())) {
                params.put("twoChannel", searchInfo.getTwoChannel());
            }
        }
        if (!"请选择".equals(searchInfo.getThrChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getThrChannel())) {
                params.put("thrChannel", searchInfo.getThrChannel());
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getAppointmentPayService())) {
            params.put("appointmentPayService", searchInfo.getAppointmentPayService());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArrivalStore())) {
            params.put("arrivalStore", searchInfo.getArrivalStore());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartFilingTime())) {
            params.put("startFilingTime", searchInfo.getStartFilingTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndFilingTime())) {
            params.put("endFilingTime", searchInfo.getEndFilingTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartAppointmentTime())) {
            params.put("startAppointmentTime", searchInfo.getStartAppointmentTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndAppointmentTime())) {
            params.put("endAppointmentTime", searchInfo.getEndAppointmentTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartFirstVisitDate())) {
            params.put("startFirstVisitDate", searchInfo.getStartFirstVisitDate());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndFirstVisitDate())) {
            params.put("endFirstVisitDate", searchInfo.getEndFirstVisitDate());
        }
        if ("0".equals(searchInfo.getPageType())) {
            params.put("condition", "");
            params.put("condition1", "order by t.filing_time desc");
        } else if ("1".equals(searchInfo.getPageType())) {
            params.put("condition", "and t.status ='到店'");
            params.put("condition1", "order by t.first_visit_date desc");
        } else {
            params.put("condition", "and t.status!='到店' and t.one_channel!='直接上门'");
            params.put("condition1", "order by t.appointment_time desc");
        }
        return dao.getQueryPagesCountYD(page, params);
    }



    public ReservationsInfo selectOneById(String reservationsId) {
        String sql = "select * from reservations_info where id= '" + reservationsId + "' limit 0,1";
        ReservationsInfo reservationsInfo = sqlMapper.selectOne(sql, ReservationsInfo.class);
        return reservationsInfo;
    }

    public int updateOneChannle() {
        String sql1 = "update reservations_info a set a.department='电销咨询' , a.follower='' where a.`status`='预约' and a.one_channel='网络部' and a.department!='电销咨询' " +
                "and a.filing_time<(DATE_SUB(now(), INTERVAL 30 DAY)) and a.filing_time>(DATE_SUB(now(), INTERVAL 60 DAY)) and a.department!='公共池'";
        String sql2 = "update reservations_info a set a.department='电销咨询' , a.follower='' where a.`status`='预约' and a.one_channel='企划' and a.department!='电销咨询' " +
                "and a.filing_time<(DATE_SUB(now(), INTERVAL 30 DAY)) and a.filing_time>(DATE_SUB(now(), INTERVAL 60 DAY)) and a.department!='公共池'";
        String sql3 = "update reservations_info a set a.department='电销咨询' , a.follower='' where a.`status`='预约' and a.one_channel='新媒体' and a.department!='电销咨询' " +
                "and a.filing_time<(DATE_SUB(now(), INTERVAL 30 DAY)) and a.filing_time>(DATE_SUB(now(), INTERVAL 60 DAY)) and a.department!='公共池'";
        String sql4 = "update reservations_info a set a.department='公共池'  where a.`status`='预约' and a.department='电销咨询'  and a.department!='公共池' " +
                "and a.filing_time<(DATE_SUB(now(), INTERVAL 60 DAY))";
        sqlMapper.update(sql1);
        sqlMapper.update(sql2);
        sqlMapper.update(sql3);
        return sqlMapper.update(sql4);
    }

    public int reservationsUpdate(ReservationsInfo reservationsInfo) {
        String filingTime = "";
        if (reservationsInfo.getFilingTime() == null || reservationsInfo.getFilingTime() == "") {
            filingTime = "null";
        } else {
            filingTime = "'" + reservationsInfo.getFilingTime() + "'";
        }
        String sql = "INSERT INTO `reservations_update_info` (`update_people`, `update_time`, `update_cname`, `update_cphone`,`archiving`,`filing_time`) VALUES " +
                "('" + ShiroKit.getUser().getAccount() + "' , '" + DateTimeKit.now() + "', '" + reservationsInfo.getCustomerName() + "', '" + reservationsInfo.getCustomerPhone() + "', '" + reservationsInfo.getArchiving() + "', " + filingTime + ");";
        return sqlMapper.insert(sql);
    }

    public List<ReservationsInfo> reservationsExport(SearchReservationsInfo searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStatuss())) {
            params.put("statuss", searchInfo.getStatuss());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStatus())) {
            params.put("status", searchInfo.getStatus());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArchiving())) {
            params.put("archiving", searchInfo.getArchiving());
        }
        if (StringUtil.isNotEmpty(searchInfo.getAccessDisease())) {
            params.put("accessDisease", searchInfo.getAccessDisease());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFollower())) {
            params.put("follower", searchInfo.getFollower());
        }
        if (StringUtil.isNotEmpty(searchInfo.getBelonger())) {
            params.put("belonger", searchInfo.getBelonger());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDepartment())) {
            params.put("department", searchInfo.getDepartment());
        }
        if (!"请选择".equals(searchInfo.getOneChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getOneChannel())) {
                params.put("oneChannel", searchInfo.getOneChannel());
            }
        }
        if (!"请选择".equals(searchInfo.getTwoChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getTwoChannel())) {
                params.put("twoChannel", searchInfo.getTwoChannel());
            }
        }
        if (!"请选择".equals(searchInfo.getThrChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getThrChannel())) {
                params.put("thrChannel", searchInfo.getThrChannel());
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getAppointmentPayService())) {
            params.put("appointmentPayService", searchInfo.getAppointmentPayService());
        }
        if (StringUtil.isNotEmpty(searchInfo.getOutpatientName())) {
            params.put("outpatientName", searchInfo.getOutpatientName());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartFilingTime())) {
            params.put("startFilingTime", searchInfo.getStartFilingTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndFilingTime())) {
            params.put("endFilingTime", searchInfo.getEndFilingTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartFirstVisitDate())) {
            params.put("startFirstVisitDate", searchInfo.getStartFirstVisitDate());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndFirstVisitDate())) {
            params.put("endFirstVisitDate", searchInfo.getEndFirstVisitDate());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartAppointmentTime())) {
            params.put("startAppointmentTime", searchInfo.getStartAppointmentTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndAppointmentTime())) {
            params.put("endAppointmentTime", searchInfo.getEndAppointmentTime());
        }
        if ("0".equals(searchInfo.getPageType())) {
            params.put("condition", "");
            params.put("condition1", "order by t.filing_time desc");
        } else if ("1".equals(searchInfo.getPageType())) {
            params.put("condition", "and t.status ='到店'");
            params.put("condition1", "order by t.first_visit_date desc");
        } else if ("2".equals(searchInfo.getPageType())) {
            params.put("condition", "and t.status!='到店' and t.one_channel!='直接上门'");
            params.put("condition1", "order by t.appointment_time desc");
        }
        return dao.getQueryPageExport(params);
    }


    public List<ReservationsInfo> reservationsExportYD(SearchReservationsInfo searchInfo) {
        Map<String, Object> params = new HashMap<String, Object>();
        if (StringUtil.isNotEmpty(searchInfo.getReach())) {
            params.put("reach", searchInfo.getReach());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStatuss())) {
            params.put("statuss", searchInfo.getStatuss());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStatus())) {
            params.put("status", searchInfo.getStatus());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArchiving())) {
            params.put("archiving", searchInfo.getArchiving());
        }
        if (StringUtil.isNotEmpty(searchInfo.getAccessDisease())) {
            params.put("accessDisease", searchInfo.getAccessDisease());
        }
        if (StringUtil.isNotEmpty(searchInfo.getFollower())) {
            params.put("follower", searchInfo.getFollower());
        }
        if (StringUtil.isNotEmpty(searchInfo.getBelonger())) {
            params.put("belonger", searchInfo.getBelonger());
        }
        if (StringUtil.isNotEmpty(searchInfo.getDepartment())) {
            params.put("department", searchInfo.getDepartment());
        }
        if (!"请选择".equals(searchInfo.getOneChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getOneChannel())) {
                params.put("oneChannel", searchInfo.getOneChannel());
            }
        }
        if (!"请选择".equals(searchInfo.getTwoChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getTwoChannel())) {
                params.put("twoChannel", searchInfo.getTwoChannel());
            }
        }
        if (!"请选择".equals(searchInfo.getThrChannel())) {
            if (StringUtil.isNotEmpty(searchInfo.getThrChannel())) {
                params.put("thrChannel", searchInfo.getThrChannel());
            }
        }
        if (StringUtil.isNotEmpty(searchInfo.getAppointmentPayService())) {
            params.put("appointmentPayService", searchInfo.getAppointmentPayService());
        }
        if (StringUtil.isNotEmpty(searchInfo.getArrivalStore())) {
            params.put("arrivalStore", searchInfo.getArrivalStore());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartFilingTime())) {
            params.put("startFilingTime", searchInfo.getStartFilingTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndFilingTime())) {
            params.put("endFilingTime", searchInfo.getEndFilingTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartFirstVisitDate())) {
            params.put("startFirstVisitDate", searchInfo.getStartFirstVisitDate());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndFirstVisitDate())) {
            params.put("endFirstVisitDate", searchInfo.getEndFirstVisitDate());
        }
        if (StringUtil.isNotEmpty(searchInfo.getStartAppointmentTime())) {
            params.put("startAppointmentTime", searchInfo.getStartAppointmentTime());
        }
        if (StringUtil.isNotEmpty(searchInfo.getEndAppointmentTime())) {
            params.put("endAppointmentTime", searchInfo.getEndAppointmentTime());
        }
        if ("0".equals(searchInfo.getPageType())) {
            params.put("condition", "");
            params.put("condition1", "order by t.filing_time desc");
        } else if ("1".equals(searchInfo.getPageType())) {
            params.put("condition", "and t.status ='到店'");
            params.put("condition1", "order by t.first_visit_date desc");
        } else if ("2".equals(searchInfo.getPageType())) {
            params.put("condition", "and t.status!='到店' and t.one_channel!='直接上门'");
            params.put("condition1", "order by t.appointment_time desc");
        }
        return dao.getQueryPageExportYD(params);
    }


    public List<ReservationsInfo> selectOneByPhone(String customerPhone) {
        String sql = "select * from(\n" +
                "select * from reservations_info where customer_phone= '" + customerPhone + "') t where one_channel='直接上门' or\n" +
                " one_channel='老带新' ;";
        List<ReservationsInfo> reservationsInfoList = sqlMapper.selectList(sql, ReservationsInfo.class);
        return reservationsInfoList;
    }


    public int insert(ReservationsInfo info) {
        String id = "预约" + CommUtils.getUUID();
        String appointmentArrivalTime = "";
        info.setStatus("预约");
        if (info.getAppointmentArrivalTime() == null || info.getAppointmentArrivalTime() == "") {
            appointmentArrivalTime = "null";
        } else {
            appointmentArrivalTime = "'" + info.getAppointmentArrivalTime() + "'";
        }
        if (info.getCustomerAge() != null) {
            for (int i = 0; i < 11; i++) {
                if (info.getCustomerAge() >= (i * 10) && info.getCustomerAge() <= (i * 10 + 9)) {
                    if (i < 10) {
                        String ageRange = (i * 10) + "-" + (i * 10 + 9);
                        info.setAgeRange(ageRange);
                    } else {
                        String ageRange = "99+";
                        info.setAgeRange(ageRange);
                    }
                }
            }
        }
        if ("null".equals(info.getHuifang()) || info.getHuifang() == null) {
            info.setHuifang("");
        }
        if ("null".equals(info.getContent()) || info.getContent() == null) {
            info.setContent("");
        }
        String selectRole = "select b.name from user a left join role b on a.roleId=b.id where a.account='" + ShiroKit.getUser().getAccount() + "'";
        Role role = sqlMapper.selectOne(selectRole, Role.class);
        if (role != null) {
            if (role.getName().indexOf("电销") != -1) {
                info.setDepartment("电销咨询");
            } else if (role.getName().indexOf("网电") != -1) {
                info.setDepartment("网电咨询");
            } else if ("公共TC".equals(role.getName())) {
                info.setDepartment("直接上门");
            } else {
                info.setDepartment(info.getOneChannel());
            }
        }
        String sql = "INSERT INTO `reservations_info` (`id`,`key_word`,`content`,`department`,`reservations_num`,`appointment_time`,`huifang`,`access_disease`," +
                "`age_range`,`appointment_pay_service`,`phone_shar_ship`,`outpatient_name`, `customer_name`, `customer_phone`, `customer_sex`," +
                " `customer_age`, `economic_ability`, `qq_number`,`wechat_number`, `other_contact`," +
                " `occupation`, `archiving`, `filing_time`, `entry_clerk`,`update_people`,`update_time`," +
                " `obstacle_point`, `one_channel`,`two_channel`,`thr_channel`, `one_medium`,`two_medium`,`thr_medium`, `is_large`, `remarks`, `classification`, " +
                "`status`,`statuss`,`appointment_arrival_time`,`is_supplement`, `follower`, `belonger`) VALUES" +
                "('" + id + "','" + info.getKeyWord() + "','" + info.getContent() + "','" + info.getDepartment() + "','" + info.getReservationsNum() + "'," + appointmentArrivalTime + ",'" + info.getHuifang() + "','" + info.getAccessDisease() + "','" + info.getAgeRange() + "','" + info.getAppointmentPayService() + "','" + info.getPhoneSharShip() + "','" + info.getOutpatientName() + "','" + info.getCustomerName() + "','" + info.getCustomerPhone() + "'," + info.getCustomerSex() + "," + info.getCustomerAge() + ",'" + info.getEconomicAbility() + "','" + info.getQqNumber() + "'," +
                "'" + info.getWechatNumber() + "','" + info.getQuarters() + "','" + info.getOccupation() + "','" + info.getArchiving() + "','" + DateTimeKit.now() + "','" + ShiroKit.getUser().getAccount() + "','" + ShiroKit.getUser().getAccount() + "','" + DateTimeKit.now() + "'," +
                "'" + info.getObstaclePoint() + "','" + info.getOneChannel() + "','" + info.getTwoChannel() + "','" + info.getThrChannel() + "','" + info.getOneMedium() + "','" + info.getTwoMedium() + "','" + info.getThrMedium() + "'," + info.getIsLarge() + "," +
                "'" + info.getRemarks() + "','" + info.getClassification() + "','预约','" + info.getStatuss() + "'," + appointmentArrivalTime + ",'否','"
                + info.getArchiving() + "','" + info.getArchiving() + "');";
        return sqlMapper.insert(sql);
    }

    public int insert(CustomerInfo info) {
        info.setStatus("到店");
        info.setArrivalStore(ShiroKit.getUser().getOutpatientName());
        if (info.getCustomerAge() != null) {
            for (int i = 0; i < 11; i++) {
                if (info.getCustomerAge() >= (i * 10) && info.getCustomerAge() <= (i * 10 + 9)) {
                    if (i < 10) {
                        String ageRange = (i * 10) + "-" + (i * 10 + 9);
                        info.setAgeRange(ageRange);
                    } else {
                        String ageRange = "99+";
                        info.setAgeRange(ageRange);
                    }
                }
            }
        }
        if ("null".equals(info.getHuifang()) || info.getHuifang() == null) {
            info.setHuifang("");
        }
        if ("null".equals(info.getWechatNumber()) || info.getWechatNumber() == null) {
            info.setWechatNumber("");
        }
        if ("null".equals(info.getAppointmentPayService()) || info.getAppointmentPayService() == null) {
            info.setAppointmentPayService("");
        }
        if ("null".equals(info.getFieldTc()) || info.getFieldTc() == null) {
            info.setFieldTc("");
        }
        String medicalRecordNumber = customerService.getMedicalRecordNumber();
        info.setDepartment(info.getOneChannel());
        String sql = "INSERT INTO `reservations_info` (`id`,`huifang`,`department`,`appointment_time`,`tc_name`,`first_visit_date`,`age_range`,`appointment_pay_service`,`phone_shar_ship`,`outpatient_name`, `customer_name`, `customer_phone`, `customer_sex`, `customer_age`, `qq_number`," +
                " `wechat_number`,`other_contact`, `country`, `province`, `cities`, `region`, `quarters`," +
                " `marital_status`, `occupation`, `archiving`, `filing_time`, `entry_clerk`,`update_people`,`update_time`, " +
                " `one_medium`,`two_medium`,`thr_medium`, `one_channel`,`two_channel`,`thr_channel`,`remarks`, `status`,`appointment_arrival_time`,`is_supplement`,identification_number,arrival_store) VALUES" +
                "('" + info.getReservationsId() + "','" + info.getHuifang() + "','" + info.getDepartment() + "','" + DateTimeKit.now() + "','" + info.getFieldTc() + "','" + DateTimeKit.now() + "','" + info.getAgeRange() + "','" + info.getAppointmentPayService() + "','" + info.getPhoneSharShip() + "','" + info.getArrivalStore() + "','" + info.getCustomerName() + "','" + info.getCustomerPhone() + "'," + info.getCustomerSex() + "," + info.getCustomerAge() + ",'" + info.getQqNumber() + "'," +
                "'" + info.getWechatNumber() + "','" + info.getOtherContact() + "','" + info.getCountry() + "','" + info.getProvince() + "','" + info.getCities() + "','" + info.getRegion() + "'," +
                "'" + info.getAddress() + "'," + info.getMaritalStatus() + ",'" + info.getOccupation() + "','" + info.getArchiving() + "','" + DateTimeKit.now() + "','" + ShiroKit.getUser().getAccount() + "','" + ShiroKit.getUser().getAccount() + "','" + DateTimeKit.now() + "'," +
                "'" + info.getOneMedium() + "','" + info.getTwoMedium() + "','" + info.getThrMedium() + "','" + info.getOneChannel() + "','" + info.getTwoChannel() + "','" + info.getThrChannel() + "'," +
                "'" + info.getRemarks() + "', '" + info.getStatus() + "','" + DateTimeKit.now() + "','否','" + medicalRecordNumber + "','"+ ShiroKit.getUser().getOutpatientName() +"');";
        return sqlMapper.insert(sql);
    }

    public List<ReservationsInfo> getCustomerPhoneCount(ReservationsInfo info) {
        String sql = "select customer_phone from reservations_info where " +
                "customer_phone='" + info.getCustomerPhone() + "' and phone_shar_ship='" + info.getPhoneSharShip() + "'";
        List<ReservationsInfo> infoList = sqlMapper.selectList(sql, ReservationsInfo.class);
        return infoList;
    }

    public List<ReservationsInfo> getCustomerPhoneCounts(String customerPhone) {
        String sql = "select id, customer_phone,filing_time from reservations_info where customer_phone like '%" + customerPhone + "%' order by filing_time desc ;";
        List<ReservationsInfo> infoList = sqlMapper.selectList(sql, ReservationsInfo.class);
        return infoList;
    }


    public int getPhoneSharShipCount(ReservationsInfo info) {
        String sql = "select * from reservations_info a where a.customer_phone='" + info.getCustomerPhone() + "'" +
                " and a.phone_shar_ship='" + info.getPhoneSharShip() + "' and a.outpatient_name='" + info.getOutpatientName() + "';";
        List<ReservationsInfo> infoList = sqlMapper.selectList(sql, ReservationsInfo.class);
        return infoList.size();
    }

    public List<ReservationsInfo> getBrCounts(String customerPhone) {
        String sql = "select customer_phone from reservations_info where customer_phone ='" + customerPhone + "' and phone_shar_ship='本人';";
        List<ReservationsInfo> infoList = sqlMapper.selectList(sql, ReservationsInfo.class);
        return infoList;
    }

    public int getUpdatePhoneCount(ReservationsInfo info) {
        String sql = "select customer_phone from reservations_info where id !='" + info.getId() + "' and customer_phone='" + info.getCustomerPhone() + "' and phone_shar_ship='本人';";
        List<ReservationsInfo> infoList = sqlMapper.selectList(sql, ReservationsInfo.class);
        return infoList.size();
    }

    public List<ReservationsInfo> selectOneByCp(List<CustomerInfo> customerInfoList, String customerPhone) {
        String sql = "select * from  (select a.id,a.customer_name,t.reservations_id,a.customer_phone,a.phone_shar_ship,a.archiving,a.filing_time " +
                " ,(\n" +
                "        CASE a.customer_sex\n" +
                "        WHEN 1 THEN\n" +
                "            '男'\n" +
                "        WHEN 2 THEN\n" +
                "            '女'\n" +
                "        ELSE\n" +
                "            '未知'\n" +
                "        END\n" +
                "    ) as sex,a.customer_age from reservations_info a left join" +
                " " + this.getTableName() + " t on a.id=t.reservations_id " +
                " where not exists(select 1 from reservations_info where customer_phone = a.customer_phone " +
                " and customer_name=a.customer_name and phone_shar_ship=a.phone_shar_ship " +
                " and filing_time < a.filing_time) and a.customer_phone like CONCAT('%','" + customerPhone + "','%')) t1 where 1=1";
        if (customerInfoList.size() > 0) {
            for (CustomerInfo customerInfo : customerInfoList) {
                sql += " and t1.reservations_id !='" + customerInfo.getReservationsId() + "' ";
            }
        }
        sql += " or t1.reservations_id is null group by t1.filing_time,t1.customer_phone,t1.archiving ;";
        List<ReservationsInfo> list = sqlMapper.selectList(sql, ReservationsInfo.class);
        return list;
    }

    @Transactional
    public void delete(String id) {
        String sql = "delete from reservations_info  where id = '" + id + "'";
        sqlMapper.delete(sql);
    }

    public int update(ReservationsInfo info) {
        String appointmentArrivalTime = "";
        if (info.getAppointmentArrivalTime() == null || info.getAppointmentArrivalTime() == "") {
            appointmentArrivalTime = "null";
        } else {
            appointmentArrivalTime = "'" + info.getAppointmentArrivalTime() + "'";
        }
        if (info.getCustomerAge() != null) {
            for (int i = 0; i < 11; i++) {
                if (info.getCustomerAge() >= (i * 10) && info.getCustomerAge() <= (i * 10 + 9)) {
                    if (i < 10) {
                        String ageRange = (i * 10) + "-" + (i * 10 + 9);
                        info.setAgeRange(ageRange);
                    } else {
                        String ageRange = "99+";
                        info.setAgeRange(ageRange);
                    }
                }
            }
        }
        info.setAppointmentArrivalTime(appointmentArrivalTime);
        info.setUpdatePeople(ShiroKit.getUser().getAccount());
        info.setUpdateTime(new Date());
        String sql2 = "";
        if ("null".equals(info.getHuifang()) || info.getHuifang() == null) {
            info.setHuifang("");
        }
        if ("电销咨询".equals(info.getDepartment())||"公共池".equals(info.getDepartment())) {
            if ("null".equals(info.getFollowers()) || info.getFollowers() == null) {
                info.setFollowers("");
            }
            sql2 = "`follower`='" + info.getFollowers() + "',";
        } else {
            sql2 = "";
        }
        String sql = "update reservations_info set  `reservations_num`='" + info.getReservationsNum() + "', `customer_name`='" + info.getCustomerName()
                + "', `customer_phone`='" + info.getCustomerPhone() + "', `customer_sex`='" + info.getCustomerSex() + "', `customer_age`="
                + info.getCustomerAge() + ", `economic_ability`='" + info.getEconomicAbility() + "', `qq_number`='" + info.getQqNumber() + "'," +
                " `age_range`='" + info.getAgeRange() + "',`outpatient_name`='" + info.getOutpatientName() + "',`phone_shar_ship`='"
                + info.getPhoneSharShip() + "',`appointment_pay_service`='" + info.getAppointmentPayService() + "',  `wechat_number`='"
                + info.getWechatNumber() + "', access_disease='" + info.getAccessDisease() + "', `archiving`='" + info.getArchiving()
                + "', `update_people`='" + ShiroKit.getUser().getAccount() + "',`update_time`='" + DateTimeKit.now()
                + "',`department`='" + info.getDepartment() + "',`obstacle_point`='" + info.getObstaclePoint()
                + "', `one_medium`='" + info.getOneMedium() + "',`two_medium`='" + info.getTwoMedium() + "',`thr_medium`='" + info.getThrMedium()
                + "', `one_channel`='" + info.getOneChannel() + "',`two_channel`='" + info.getTwoChannel() + "',`thr_channel`='" + info.getThrChannel()
                + "', `is_large`='" + info.getIsLarge() + "', `remarks`='" + info.getRemarks() + "'," + sql2 +
                " `content`='" + info.getContent() + "',`key_word`='" + info.getKeyWord() + "'," +
                " `appointment_arrival_time`=" + appointmentArrivalTime + ", " +
                "`appointment_time`=" + appointmentArrivalTime + ",belonger='" + info.getBelonger() + "' where id='" + info.getId() + "'";
        return sqlMapper.update(sql);
    }

    public int adminUpdate(ReservationsInfo info) {
        String appointmentArrivalTime = "";
        String filingTime = "";
        if (info.getAppointmentArrivalTime() == null || info.getAppointmentArrivalTime() == "") {
            appointmentArrivalTime = "null";
        } else {
            appointmentArrivalTime = "'" + info.getAppointmentArrivalTime() + "'";
        }
        if (info.getFilingTime() == null || info.getFilingTime() == "") {
            filingTime = "null";
        } else {
            filingTime = "'" + info.getFilingTime() + "'";
        }
        if (info.getCustomerAge() != null) {
            for (int i = 0; i < 11; i++) {
                if (info.getCustomerAge() >= (i * 10) && info.getCustomerAge() <= (i * 10 + 9)) {
                    if (i < 10) {
                        String ageRange = (i * 10) + "-" + (i * 10 + 9);
                        info.setAgeRange(ageRange);
                    } else {
                        String ageRange = "99+";
                        info.setAgeRange(ageRange);
                    }
                }
            }
        }
        info.setAppointmentArrivalTime(appointmentArrivalTime);
        info.setUpdatePeople(ShiroKit.getUser().getAccount());
        info.setUpdateTime(new Date());
        String sql = "update reservations_info set `customer_name`='" + info.getCustomerName() + "', `customer_phone`='" + info.getCustomerPhone() + "', `customer_sex`='" + info.getCustomerSex() + "', `customer_age`=" + info.getCustomerAge() + ", `economic_ability`='" + info.getEconomicAbility() + "', `qq_number`='" + info.getQqNumber() + "'," +
                " `age_range`='" + info.getAgeRange() + "',`outpatient_name`='" + info.getOutpatientName() + "',`phone_shar_ship`='" + info.getPhoneSharShip() + "',`appointment_pay_service`='" + info.getAppointmentPayService() + "',  `wechat_number`='" + info.getWechatNumber() + "', `other_contact`='" + info.getOtherContact() + "', `country`='" + info.getCountry() + "', `province`='" + info.getProvince() + "', `cities`='" + info.getCities() + "', `region`='" + info.getRegion() + "', `quarters`='" + info.getQuarters() + "'," +
                " `marital_status`='" + info.getMaritalStatus() + "', `occupation`='" + info.getOccupation() + "', `archiving`='" + info.getArchiving() + "', `update_people`='" + ShiroKit.getUser().getAccount() + "',`update_time`='" + DateTimeKit.now() + "', `vehicle`='" + info.getVehicle() + "', " +
                "  access_disease='" + info.getAccessDisease() + "', `obstacle_point`='" + info.getObstaclePoint() + "', `one_medium`='" + info.getOneMedium() + "',`two_medium`='" + info.getTwoMedium() + "',`thr_medium`='" + info.getThrMedium() + "', `one_channel`='" + info.getOneChannel() + "',`two_channel`='" + info.getTwoChannel() + "',`thr_channel`='" + info.getThrChannel() + "', `is_large`='" + info.getIsLarge() + "', `remarks`='" + info.getRemarks() + "', `classification`='" + info.getClassification() + "'," +
                " `appointment_arrival_time`=" + appointmentArrivalTime + ", `appointment_time`=" + appointmentArrivalTime + " where id='" + info.getId() + "'";
        String sql1 = "update " + this.getTableName() + " set `one_channel`='" + info.getOneChannel() + "',`two_channel`='" + info.getTwoChannel() + "'," +
                "`thr_channel`='" + info.getThrChannel() + "',`customer_name`='" + info.getCustomerName() + "', " +
                "`customer_phone`='" + info.getCustomerPhone() + "', `customer_sex`='" + info.getCustomerSex() + "'," +
                " `age_range`='" + info.getAgeRange() + "',`customer_age`=" + info.getCustomerAge() + " where reservations_id='" + info.getId() + "';";
        sqlMapper.update(sql1);
        return sqlMapper.update(sql);
    }


    public int supplementUpdate(ReservationsInfo info) {
        String appointmentArrivalTime = "";
        String filingTime = "";
        if (info.getAppointmentArrivalTime() == null || info.getAppointmentArrivalTime() == "") {
            appointmentArrivalTime = "null";
        } else {
            appointmentArrivalTime = "'" + info.getAppointmentArrivalTime() + "'";
        }
        if (info.getFilingTime() == null || info.getFilingTime() == "") {
            filingTime = "null";
        } else {
            filingTime = "'" + info.getFilingTime() + "'";
        }
        if (info.getCustomerAge() != null) {
            for (int i = 0; i < 11; i++) {
                if (info.getCustomerAge() >= (i * 10) && info.getCustomerAge() <= (i * 10 + 9)) {
                    if (i < 10) {
                        String ageRange = (i * 10) + "-" + (i * 10 + 9);
                        info.setAgeRange(ageRange);
                    } else {
                        String ageRange = "99+";
                        info.setAgeRange(ageRange);
                    }
                }
            }
        }
        String selectRole = "select b.name from user a left join role b on a.roleId=b.id where a.account='" + info.getArchiving() + "' limit 0,1";
        Role role = sqlMapper.selectOne(selectRole, Role.class);
        if (role != null) {
            if (role.getName().indexOf("电销") != -1) {
                info.setDepartment("电销咨询");
            } else if (role.getName().indexOf("网电") != -1) {
                info.setDepartment("网电咨询");
            } else if ("公共TC".equals(role.getName())) {
                info.setDepartment("直接上门");
            } else {
                info.setDepartment(info.getOneChannel());
            }
        }
        info.setAppointmentArrivalTime(appointmentArrivalTime);
        info.setUpdatePeople(ShiroKit.getUser().getAccount());
        info.setUpdateTime(new Date());
        String sql = "update reservations_info set  `filing_time`=" + filingTime + ",  `archiving`='" + info.getArchiving() + "',`follower`='" + info.getArchiving() + "', `update_people`='" + ShiroKit.getUser().getAccount() + "',`update_time`='" + DateTimeKit.now() + "', " +
                "`one_channel`='" + info.getOneChannel() + "',`two_channel`='" + info.getTwoChannel() + "',`thr_channel`='" + info.getThrChannel() + "', `remarks`='" + info.getRemarks() + "', " +
                "`is_supplement`='是', `department`='" + info.getDepartment() + "', `appointment_arrival_time`=" + appointmentArrivalTime + ", `appointment_time`=" + appointmentArrivalTime + " where id='" + info.getId() + "'";
        String sql1 = "update " + this.getTableName() + " set `one_channel`='" + info.getOneChannel() + "',`two_channel`='" + info.getTwoChannel() + "'," +
                "`thr_channel`='" + info.getThrChannel() + "'  where reservations_id='" + info.getId() + "';";
        sqlMapper.update(sql1);
        return sqlMapper.update(sql);
    }

    public int insertDelete(ReservationsInfo reservations) {
        String sql = "INSERT INTO `reservations_delete_info` (`delete_people`, `delete_time`, `delete_cname`, `delete_cphone`,`archiving`,`filing_time`) VALUES " +
                "('" + ShiroKit.getUser().getAccount() + "' , '" + DateTimeKit.now() + "', '" + reservations.getCustomerName() + "', '" + reservations.getCustomerPhone() + "', '" + reservations.getArchiving() + "', '" + reservations.getFilingTime() + "');";
        return sqlMapper.delete(sql);
    }

    public String getTableName() {
        String tableHead = ShiroKit.getUser().getAbbreviation();
        String tableName = tableHead + "customer_info";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        return tableName;
    }
}
